/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package trim.gui.testing.export;

import java.io.ByteArrayOutputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import trim.Trim;
import trim.common.inputdata.FormatOrder;
import trim.common.inputdata.MaterialStock;
import trim.common.inputdata.InputData;
import trim.common.result.Pattern;
import trim.common.result.PatternItem;
import trim.common.result.TrimResult;
import trim.common.result.TrimmedFormatOrder;
import trim.gui.testing.objects.ResultsListItem;
import trim.scripting.interfaces.IExportManager;

/**
 * 
 * Created 25.01.2009 20:04:39
 * @author Саковнич Дмитрий
 */
public class ExportManager implements IExportManager
{

  public static void exportResultListToXLS(OutputStream os,
    List<Map<String,ResultsListItem>> results,List<Trim> methods) throws IOException
  {
    HSSFWorkbook wb=new HSSFWorkbook();
    HSSFSheet sheet=wb.createSheet();
    HSSFCellStyle style=getBorderarountStyle(wb);
    HSSFCellStyle centerboldstyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_CENTER);
    HSSFCellStyle leftboldstyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_LEFT);
    HSSFCellStyle rightboldstyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_RIGHT);

    //sheet.setColumnWidth((short)1,(short)(256*100));
    //sheet.setColumnWidth((short)2,(short)(256*14));

    HSSFPrintSetup ps=sheet.getPrintSetup();
    sheet.setAutobreaks(true);
    ps.setFitHeight((short)1);
    ps.setFitWidth((short)1);
    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row=null;


    int j;

    int currentRow=4;

//      row = sheet.createRow(0);
//      row.createCell((short)1).setCellValue(new HSSFRichTextString("Отчет"));
//      row.getCell((short)1).setCellStyle(getBoldStyle(wb,HSSFCellStyle.ALIGN_CENTER,(short)16));
//      row = sheet.createRow(1);
//      row.createCell((short)1).setCellValue(new HSSFRichTextString("ё "+sdf.format(from)+" яю "+sdf.format(to)));
//      row.getCell((short)1).setCellStyle(getBoldStyle(wb,HSSFCellStyle.ALIGN_CENTER,(short)16));

    row=sheet.createRow(currentRow++);
    row.createCell((short)0).setCellValue(new HSSFRichTextString("№"));
    row.createCell((short)1).setCellValue(new HSSFRichTextString("форматов"));
    row.createCell((short)2).setCellValue(new HSSFRichTextString("материалов"));
    row.createCell((short)3).setCellValue(new HSSFRichTextString("заготовок"));
    row.createCell((short)4).setCellValue(new HSSFRichTextString("материалов"));
    sheet.addMergedRegion(new Region(currentRow-1,(short)0,currentRow,(short)0));
    sheet.addMergedRegion(new Region(currentRow-1,(short)1,currentRow,(short)1));
    sheet.addMergedRegion(new Region(currentRow-1,(short)2,currentRow,(short)2));
    sheet.addMergedRegion(new Region(currentRow-1,(short)3,currentRow,(short)3));
    sheet.addMergedRegion(new Region(currentRow-1,(short)4,currentRow,(short)4));
    int col=5;
    HSSFRow row1=sheet.createRow(currentRow++);
    for(int i=0;i<5;i++)
    {
      row.getCell((short)(0+i)).setCellStyle(centerboldstyle);
      row1.createCell((short)(0+i)).setCellStyle(centerboldstyle);
    }
    String[] colNames=
    {
      "съемы","отход,мм","раскладок","заготовок","эффективность,%","время,мс"
    };
    for(String s:colNames)
    {
      row.createCell((short)(col+0)).setCellValue(new HSSFRichTextString(s));
      sheet.addMergedRegion(new Region(currentRow-2,(short)col,currentRow-2,
        (short)(col+methods.size()-1)));
      int i=0;
      for(Trim t:methods)
      {
        row1.createCell((short)(col+i)).setCellValue(new HSSFRichTextString(t.getName()));

        row1.getCell((short)(col+i)).setCellStyle(centerboldstyle);
        if(i>0)
          row.createCell((short)(col+i)).setCellStyle(centerboldstyle);
        else
          row.getCell((short)(col+i)).setCellStyle(centerboldstyle);
        i++;
      }
      col+=methods.size();
    }
    int k=0;
    for(Map<String,ResultsListItem> m:results)
    {
      k++;
      row=sheet.createRow(currentRow++);
      InputData d=null;
      if(m.size()==0)
        continue;
      col=5;
      int mc=methods.size();
      for(Trim method:methods)
      {
        ResultsListItem r=m.get(method.getName());
        if(r==null)
        {
          col+=1;
          continue;
        }
        d=r.getInputData();
        setExcelCellValue(row,col+0*mc,r.getUsedMaterialsCount(),style);
        setExcelCellValue(row,col+1*mc,r.getTail(),style);
        setExcelCellValue(row,col+2*mc,r.size(),style);
        setExcelCellValue(row,col+3*mc,r.getTotalTrimmedItemsCount(),style);
        if(r.getEfficiency()>1)
          setExcelCellValue(row,col+4*mc,0,style);
        else
          setExcelCellValue(row,col+4*mc,Math.round(r.getEfficiency()*10000)/100,style);
        setExcelCellValue(row,col+5*mc,r.getTime(),style);
        col++;
      }
      if(d==null)
        continue;
      setExcelCellValue(row,0,k,style);
      setExcelCellValue(row,1,d.getDiffFormatLengthCount(),style);
      setExcelCellValue(row,2,d.getDiffMaterialLengthCount(),style);
      setExcelCellValue(row,3,d.getFormatCountSum(),style);
      setExcelCellValue(row,4,d.getMaterialCountSum(),style);
    }
    /*
    for(int i=0;i<rs.getRecordCount();i++)
    {
    if(!s.equals(rs.getValue(i,0)))
    {
    s=rs.getValue(i,0);
    int k=0;
    k=Integer.parseInt(rs.getValue(i,3));
    j=i+1;
    while(j<rs.getRecordCount()&&s.equals(rs.getValue(j,0)))
    {
    k+=Integer.parseInt(rs.getValue(j,3));
    j++;
    }
    //new row
    row = sheet.createRow(currentRow++);
    row.createCell((short)0).setCellValue(new HSSFRichTextString(rs.getValue(i,0)));
    row.createCell((short)1);
    row.createCell((short)2).setCellValue(k);
    row.getCell((short)0).setCellStyle(leftboldstyle);
    row.getCell((short)1).setCellStyle(leftboldstyle);
    row.getCell((short)2).setCellStyle(rightboldstyle);
    sheet.addMergedRegion(new Region(currentRow-1,(short)0,currentRow-1,(short)1));
    }

    row = sheet.createRow(currentRow++);
    row.createCell((short)0).setCellValue(new HSSFRichTextString(rs.getValue(i,1)));
    row.createCell((short)1).setCellValue(new HSSFRichTextString(rs.getValue(i,2)));
    row.createCell((short)2).setCellValue(Integer.parseInt(rs.getValue(i,3)));
    row.getCell((short)0).setCellStyle(style);
    row.getCell((short)1).setCellStyle(style);
    row.getCell((short)2).setCellStyle(style);
    }*/
    writeWorbookToStream(wb,os);

  }

  /**
   * Выгрузка решения в Excel
   * @param fileOutputStream
   * @param trimResult
   */
  public static void exportResultToXLS(OutputStream os,ResultsListItem trimResult)
  {
    HSSFWorkbook wb=new HSSFWorkbook();
    HSSFSheet sheet=wb.createSheet();
    HSSFCellStyle borderaroundStyle=getBorderarountStyle(wb);
    HSSFCellStyle boldStyle=createStyle(wb,HSSFCellStyle.ALIGN_LEFT,true,false,false,false);
    HSSFCellStyle boldStyleRight=createStyle(wb,HSSFCellStyle.ALIGN_RIGHT,true,false,false,false);
    HSSFCellStyle centerBoldBorderaroundStyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_CENTER);
    HSSFCellStyle leftBoldBorderaroundStyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_LEFT);
    HSSFCellStyle rightBoldBorderaroundStyle=getBoldBorderarountStyle(wb,HSSFCellStyle.ALIGN_RIGHT);

    //sheet.setColumnWidth((short)1,(short)(256*100));
    sheet.setColumnWidth((short)0,(short)(256*20));

    HSSFPrintSetup ps=sheet.getPrintSetup();
    sheet.setAutobreaks(true);
    ps.setFitHeight((short)1);
    ps.setFitWidth((short)1);

    // Create a row and put some cells in it. Rows are 0 based.
    HSSFRow row=null;

    int currentRow=1;
    int col=0;
    HSSFRow[] rows=new HSSFRow[5];
    //Вектор требований
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col,"Вектор требований",boldStyle);
    for(int i=0;i<4;i++)
      rows[i]=sheet.createRow(currentRow++);
    setExcelCellValue(rows[0],col,"Формат",leftBoldBorderaroundStyle);
    setExcelCellValue(rows[1],col,"Количество",leftBoldBorderaroundStyle);
    setExcelCellValue(rows[2],col,"Отклонение-",leftBoldBorderaroundStyle);
    setExcelCellValue(rows[3],col,"Отклонение+",leftBoldBorderaroundStyle);
    int k=1;
    for(FormatOrder fc:trimResult.getInputData().getFormatOrderList())
    {
      setExcelCellValue(rows[0],col+k,fc.getFormat().getLength(),rightBoldBorderaroundStyle);
      setExcelCellValue(rows[1],col+k,fc.getCount(),borderaroundStyle);
      setExcelCellValue(rows[2],col+k,fc.getDeltaM(),borderaroundStyle);
      setExcelCellValue(rows[3],col+k,fc.getDeltaP(),borderaroundStyle);
      k++;
    }
    //Материалы
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col,"Материалы",boldStyle);
    for(int i=0;i<2;i++)
      rows[i]=sheet.createRow(currentRow++);
    setExcelCellValue(rows[0],col,"Размер",leftBoldBorderaroundStyle);
    setExcelCellValue(rows[1],col,"Количество",leftBoldBorderaroundStyle);
    k=1;
    for(MaterialStock mc:trimResult.getInputData().getMaterialStockList())
    {
      setExcelCellValue(rows[0],col+k,mc.getMaterial().getLength(),rightBoldBorderaroundStyle);
      setExcelCellValue(rows[1],col+k,mc.getCount(),borderaroundStyle);
      k++;
    }
    currentRow++;
    //Таблица с раскроями
    //шапка
    row=sheet.createRow(currentRow++);

    setExcelCellValue(row,col+0,"№",centerBoldBorderaroundStyle);
    setExcelCellValue(row,col+1,"Съемов",centerBoldBorderaroundStyle);
    sheet.addMergedRegion(new Region(currentRow-1,(short)(col+2),currentRow-1,(short)(col+9)));
    setRegionStyle(sheet,col+2,currentRow-1,9,1,borderaroundStyle);
    setExcelCellValue(row,col+10,"Материал",centerBoldBorderaroundStyle);
    setExcelCellValue(row,col+11,"Отход",centerBoldBorderaroundStyle);
    setExcelCellValue(row,col+12,"Использовано",centerBoldBorderaroundStyle);
    setExcelCellValue(row,col+13,"Эффективность,%",centerBoldBorderaroundStyle);
    k=1;
    for(Pattern p:trimResult)
    {
      row=sheet.createRow(currentRow++);
      setExcelCellValue(row,col+0,k,borderaroundStyle);
      setExcelCellValue(row,col+1,p.getCount(),borderaroundStyle);
      setExcelCellValue(row,col+10,p.getMaterial().getLength(),borderaroundStyle);
      setExcelCellValue(row,col+11,p.getPatternTail(),borderaroundStyle);
      setExcelCellValue(row,col+12,p.getPatternUsedArea(),borderaroundStyle);
      setExcelCellValue(row,col+13,Math.round(p.getPatternEfficiency()*10000)/(double)100,
        borderaroundStyle);
      //вывод раскладки
      int i=2;
      for(TrimmedFormatOrder pi:p.getTrimmedFormatOrders())
      {
        setExcelCellValue(row,col+i,pi.getFormat().getLength()+"*"+pi.getCount(),borderaroundStyle);
        i++;
      }
      setRegionStyle(sheet,col+i,currentRow-1,9-i+1,1,borderaroundStyle);
      k++;
    }
    //вывод общей информации по решению
    currentRow++;
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Количество съемов",boldStyle);
    setExcelCellValue(row,col+4,trimResult.getUsedMaterialsCount(),boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Различных съемов",boldStyle);
    setExcelCellValue(row,col+4,trimResult.size(),boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Площадь съемов",boldStyle);
    setExcelCellValue(row,col+4,
      trimResult.getUsedMaterialsCount()*trimResult.getInputData().getMaterialStockList().get(0).getMaterial().getLength(),
      boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Полезная площадь",boldStyle);
    setExcelCellValue(row,col+4,
      trimResult.getUsedMaterialsCount()*trimResult.getInputData().getMaterialStockList().get(0).getMaterial().getLength()-trimResult.getTail(),
      boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Отход",boldStyle);
    setExcelCellValue(row,col+4,trimResult.getTail(),boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Эффективность",boldStyle);
    setExcelCellValue(row,col+4,Math.round(trimResult.getEfficiency()*100)+"%",boldStyleRight);
    row=sheet.createRow(currentRow++);
    setExcelCellValue(row,col+1,"Время выполнения,мс",boldStyle);
    setExcelCellValue(row,col+4,trimResult.getTime(),boldStyleRight);
    writeWorbookToStream(wb,os);

  }

  private static void writeWorbookToStream(HSSFWorkbook wb,OutputStream os)
  {
    try
    {
      wb.write(os);
    }catch(IOException ex)
    {
      ex.printStackTrace();
    }

  }

  private static HSSFCellStyle getBorderarountStyle(HSSFWorkbook wb)
  {
    HSSFCellStyle style=wb.createCellStyle();
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    style.setBottomBorderColor(HSSFColor.BLACK.index);
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    style.setLeftBorderColor(HSSFColor.BLACK.index);
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    style.setRightBorderColor(HSSFColor.BLACK.index);
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    style.setTopBorderColor(HSSFColor.BLACK.index);
    style.setWrapText(true);
    return style;
  }

  private static HSSFCellStyle getBoldBorderarountStyle(HSSFWorkbook wb,short align)
  {
    HSSFCellStyle style=getBorderarountStyle(wb);
    HSSFFont font=getBoldFont(wb);
    style.setFont(font);
    style.setAlignment(align);
    return style;
  }

  private static HSSFFont getBoldFont(HSSFWorkbook wb)
  {
    HSSFFont font=wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    return font;
  }

  private static HSSFCellStyle getBoldStyle(HSSFWorkbook wb,short align,short size)
  {
    HSSFCellStyle style=wb.createCellStyle();
    HSSFFont font=wb.createFont();
    if(size>0)
      font.setFontHeightInPoints(size);
    //font.setFontName("Courier New");
    //font.setItalic(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    style.setAlignment(align);
    style.setFont(font);
    //font.setStrikeout(true);
    return style;
  }

  /**
   * Создание стиля ячейки
   * @param wb
   * @param textAlign
   * @param bold
   * @param italic
   * @param borderAround
   * @return
   */
  private static HSSFCellStyle createStyle(HSSFWorkbook wb,
    int textAlign,boolean bold,boolean italic,boolean borderAround,boolean wrapText)
  {
    HSSFCellStyle style=wb.createCellStyle();
    if(borderAround)
    {
      style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
      style.setBottomBorderColor(HSSFColor.BLACK.index);
      style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
      style.setLeftBorderColor(HSSFColor.BLACK.index);
      style.setBorderRight(HSSFCellStyle.BORDER_THIN);
      style.setRightBorderColor(HSSFColor.BLACK.index);
      style.setBorderTop(HSSFCellStyle.BORDER_THIN);
      style.setTopBorderColor(HSSFColor.BLACK.index);
    }
    HSSFFont font=wb.createFont();
    if(bold)
      font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setItalic(italic);
    style.setFont(font);
    style.setAlignment((short)textAlign);
    style.setWrapText(wrapText);
    return style;
  }

  private static void setRegionStyle(HSSFSheet sheet,int left,int top,int width,int height,
    HSSFCellStyle style)
  {
    for(int i=0;i<height;i++)
    {
      HSSFRow row=sheet.getRow(top+i);
      for(int j=0;j<width;j++)
      {
        setExcelCellValue(row,left+j,style);
      }
    }
  }

  private static void setExcelCellValue(HSSFRow row,int col,String value,HSSFCellStyle style)
  {
    row.createCell(col).setCellValue(new HSSFRichTextString(value));
    row.getCell(col).setCellStyle(style);
  }

  private static void setExcelCellValue(HSSFRow row,int col,int value,HSSFCellStyle style)
  {
    row.createCell(col).setCellValue(value);
    row.getCell(col).setCellStyle(style);
  }

  private static void setExcelCellValue(HSSFRow row,int col,long value,HSSFCellStyle style)
  {
    row.createCell(col).setCellValue(value);
    row.getCell(col).setCellStyle(style);
  }

  private static void setExcelCellValue(HSSFRow row,int col,double value,HSSFCellStyle style)
  {
    row.createCell(col).setCellValue(value);
    row.getCell(col).setCellStyle(style);
  }

  private static void setExcelCellValue(HSSFRow row,int col,HSSFCellStyle style)
  {
    row.createCell(col).setCellStyle(style);
  }

  @Override
  public void exportToExcel(String fileName,List<Object[]> data) throws Exception
  {
    HSSFWorkbook wb=new HSSFWorkbook();
    HSSFSheet sheet=wb.createSheet();
    HSSFCellStyle style=getBorderarountStyle(wb);
    int nrow=1;
    for(Object[] ar:data)
    {
      int col=1;
      HSSFRow row=sheet.createRow(nrow++);
      for(Object o:ar)
      {
        if(o!=null)
          if(o.getClass().equals(Double.class))
            setExcelCellValue(row,col,((Double)o).doubleValue(),style);
          else if(o.getClass().equals(Integer.class))
            setExcelCellValue(row,col,((Integer)o).intValue(),style);
          else if(o.getClass().equals(Long.class))
            setExcelCellValue(row,col,((Long)o).longValue(),style);
          else if(o.getClass().equals(String.class))
            setExcelCellValue(row,col,(String)o,style);
        col++;
      }
    }
    OutputStream os=new FileOutputStream(fileName);
    writeWorbookToStream(wb,os);
    os.close();
  }

  @Override
  public void exportTrimResultToExcel(String fileName,String testName,Object inputData,
    Object trimAlgorithm,Object trimResult,long time) throws FileNotFoundException,IOException
  {
    ResultsListItem ri=new ResultsListItem((TrimResult)trimResult,(Trim)trimAlgorithm,
      (InputData)inputData,time);
    OutputStream os=new FileOutputStream(fileName);
    exportResultToXLS(os,ri);
    os.close();
  }

  @Override
  public void exportToCSV(Object[][] data)
  {
    throw new UnsupportedOperationException("Not supported yet.");
  }
}
